package uit.jee.lib.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.jee.lib.dto.UserDTO;

/**
 *
 * @author ngloc_it
 */
public class UserMapper extends MapperDB{

    public UserMapper() throws Exception {
        super();
    }

    private void InitUserDTOFromRs(UserDTO user, ResultSet  rs) throws Exception{
        if((rs != null) && (user != null)){
            user.setUserId(rs.getString(UserDTO.USER_ID));
            user.setUserName(rs.getString(UserDTO.USER_USERNAME));
            user.setBirthDay(rs.getString(UserDTO.USER_BIRTHDAY));
            user.setCMND(rs.getString(UserDTO.USER_CMND));
            user.setEmail(rs.getString(UserDTO.USER_EMAIL));
            user.setName(rs.getString(UserDTO.USER_FULLNAME));
            user.setPassword(rs.getString(UserDTO.USER_PASSWORD));
            user.setType(rs.getString(UserDTO.USER_TYPE));
            user.setAddress(rs.getString(UserDTO.USER_ADDRESS));
        }
    }

    public boolean CheckExistedUser(UserDTO user) throws Exception{
        boolean isExisted = false;
        try{
        StringBuffer sql = new StringBuffer();
        sql.append("select * from booklibrary.user where ");
        sql.append(UserDTO.USER_ID).append("= '").append(user.getUserId()).append("'");
        
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        ResultSet rs = stmt.executeQuery();
        if(rs!=null && rs.next())
            isExisted = true;
        }catch(Exception ex)
        {
            throw ex;
        }
        return isExisted;
    }

    public ArrayList<UserDTO> GetAllUser() throws Exception{
        ArrayList<UserDTO> users = new ArrayList<UserDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.user ");
           
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re= stmt.executeQuery();
            while(re!=null && re.next())
            {
                UserDTO user = new UserDTO();
                InitUserDTOFromRs(user, re);
                users.add(user);
            }
        }catch(Exception ex)
        {
            throw ex;
        }
        
        return users;
    }

    public UserDTO GetUserById(String userId) throws Exception{
        UserDTO user = new UserDTO();
        try{ 
            StringBuffer sql= new StringBuffer();
            sql.append("select * from booklibrary.user where");
            sql.append(UserDTO.USER_ID).append("= '").append(userId).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re= stmt.executeQuery();
            if(re!= null && re.next())
                InitUserDTOFromRs(user, re);
       }catch(Exception ex)
       {
           throw ex;
       }
       return user;
        
    }

    public UserDTO GetUserByUserName(String userName) throws Exception{
        UserDTO user = new UserDTO();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.user where ");
            sql.append(UserDTO.USER_USERNAME).append("= '").append(userName).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re= stmt.executeQuery();
            if(re!=null && re.next())
                InitUserDTOFromRs(user, re);
        }catch(Exception ex)
        {
            throw ex;
        }
        return user;
    }

    public ArrayList<UserDTO> GetUserByFullName(String name) throws Exception{
        ArrayList<UserDTO> users = new ArrayList<UserDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.user where");
            sql.append(UserDTO.USER_FULLNAME).append("= '").append(name).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re= stmt.executeQuery();
            while(re!=null && re.next())
            {
                UserDTO user = new UserDTO();
                InitUserDTOFromRs(user, re);
                users.add(user);
            }
        }catch(Exception ex)
        {
            throw ex;
        }
        
        return users;
        
        
    }

    public ArrayList<UserDTO> GetUserByEmail(String email) throws Exception{
        ArrayList<UserDTO> users = new ArrayList<UserDTO>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from booklibrary.user where");
            sql.append(UserDTO.USER_EMAIL).append("= '").append(email).append("'");
            
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re= stmt.executeQuery();
            while(re!=null && re.next())
            {
                UserDTO user = new UserDTO();
                InitUserDTOFromRs(user, re);
                users.add(user);
            }
        }catch(Exception ex)
        {
            throw ex;
        }
        
        return users;
    }

    public void Update(UserDTO user) throws Exception{
           try{
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.user Set ");
            sql.append(UserDTO.USER_ID).append("='").append(user.getUserId()).append("', ");
            sql.append(UserDTO.USER_TYPE).append("='").append(user.getType()).append("', ");
            sql.append(UserDTO.USER_USERNAME).append("='").append(user.getUserName()).append("', ");
            sql.append(UserDTO.USER_PASSWORD).append("='").append(user.getPassword()).append("', ");
            sql.append(UserDTO.USER_FULLNAME).append("='").append(user.getName()).append("', ");
            sql.append(UserDTO.USER_BIRTHDAY).append("='").append(user.getBirthDay()).append("', ");
            sql.append(UserDTO.USER_EMAIL).append("='").append(user.getEmail()).append("', ");
            sql.append(UserDTO.USER_CMND).append("='").append(user.getCMND()).append("', ");
            sql.append(UserDTO.USER_ADDRESS).append("='").append(user.getAddress()).append("', ");
            
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        }catch(Exception ex){
            throw ex;
        } 
    }

    public void Update(UserDTO[] users) throws Exception{

    }

    public void InsertUser(UserDTO user) throws Exception{
    try {
        StringBuffer sql = new StringBuffer();
        sql.append("Insert into booklibrary.user values( ");
        sql.append("'").append(user.getUserId()).append("', ");
        sql.append("'").append(user.getType()).append("', ");
        sql.append("'").append(user.getUserName()).append("', ");
        sql.append("'").append(user.getPassword()).append("', ");
        sql.append("'").append(user.getName()).append("', ");
        sql.append("'").append(user.getBirthDay()).append("', ");
        sql.append("'").append(user.getEmail()).append("', ");
        sql.append("'").append(user.getCMND()).append("', ");
        sql.append("'").append(user.getAddress()).append("' )");
        
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        stmt.execute();
        stmt.close();
        }
        catch (Exception e) {
            throw e;
        }
    }

    public void InsertUser(UserDTO[] users) throws Exception{

    }

    public void Delete(UserDTO user) throws Exception{
      try{
        StringBuffer sql = new StringBuffer();
        sql.append("delete * from booklibrary.user where ");
        sql.append(UserDTO.USER_ID).append(" = ' ").append(user.getUserId()).append("'");
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        stmt.execute();
        stmt.close();
        }catch(Exception ex)
        {
            throw ex;
        }
    }

    public void Delete(UserDTO[] users) throws Exception{

    }

    public void Delete(String userName) throws Exception{
      try{
        StringBuffer sql = new StringBuffer();
        sql.append("delete * from booklibrary.user where ");
        sql.append(UserDTO.USER_USERNAME).append(" = ' ").append(userName).append("'");
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        stmt.execute();
        stmt.close();
        }catch(Exception ex)
        {
            throw ex;
        }
    }
}
